package com.huike.pmps.dao;

import com.huike.pmps.model.dto.cockpit.province.BusinessOpportunityLineProvinceDTO;
import com.huike.pmps.model.dto.cockpit.region.*;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 作者：谌贵斌(黑桃K)
 * 日期: 2888-08-08
 * 老师QQ: 272488352
 * 官方QQ交流群: 377748272
 * 刀客程序员官网：http://www.daoke360.com
 * 刀客程序员淘宝旗舰店：https://daoke360.taobao.com/
 */
public interface CockpitRegionDao {


    @Select({
            "<script>",
            "SELECT region_name,collection_amount,payment_amount,salary,																						",
            "(IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))gross_profit,                                                               ",
            "ROUND((IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))/IFNULL(collection_amount,0),2) gross_profit_rate                     ",
            "FROM(                                                                                                                                              ",
            "   SELECT                                                                                                                                          ",
            "   region_name,                                                                                                                                    ",
            "   SUM(collection_amount)  collection_amount,                                                                                                      ",
            "   (SELECT SUM(payment_amount)    FROM `p_expenses` WHERE region_id=#{regionId}  AND `year`=#{year}  AND   month_of_year=#{month}) payment_amount, ",
            "   (SELECT SUM(salary)    FROM `p_personnel_cost`   WHERE region_id=#{regionId}  AND `year`=#{year}  AND   month_of_year=#{month}) salary          ",
            "   FROM `p_payment_collection`    WHERE region_id=#{regionId}  AND `year`=#{year}  AND   month_of_year=#{month}                                    ",
            ")temp                                                                                                                                              ",
            "                                                                                                                                                   ",
            "UNION ALL                                                                                                                                          ",
            "                                                                                                                                                   ",
            "SELECT region_name,collection_amount,payment_amount,salary,																			            ",
            "(IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))gross_profit,                                                               ",
            "ROUND((IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))/IFNULL(collection_amount,0),2) gross_profit_rate                     ",
            "FROM                                                                                                                                               ",
            "(                                                                                                                                                  ",
            "	SELECT pr.province_name region_name ,pc.collection_amount,ep.payment_amount,pct.salary                                                          ",
            "	FROM p_region pr                                                                                                                                ",
            "	LEFT JOIN                                                                                                                                       ",
            "	(                                                                                                                                               ",
            "		SELECT                                                                                                                                      ",
            "		province_name,                                                                                                                              ",
            "		SUM(collection_amount)  collection_amount                                                                                                   ",
            "		FROM `p_payment_collection`                                                                                                                 ",
            "		WHERE region_id=#{regionId}  AND `year`=#{year}  AND   month_of_year=#{month}                                                               ",
            "		GROUP BY province_name                                                                                                                      ",
            "	) pc ON pr.province_name=pc.province_name                                                                                                       ",
            "	LEFT JOIN(                                                                                                                                      ",
            "		SELECT province_name,SUM(payment_amount)payment_amount                                                                                      ",
            "		FROM `p_expenses`                                                                                                                           ",
            "		WHERE region_id=#{regionId}  AND `year`=#{year}  AND   month_of_year=#{month}                                                               ",
            "		GROUP BY province_name                                                                                                                      ",
            "	)ep ON pr.province_name=ep.province_name                                                                                                        ",
            "	LEFT JOIN                                                                                                                                       ",
            "	(                                                                                                                                               ",
            "		SELECT  province_name,SUM(salary)salary                                                                                                     ",
            "		FROM `p_personnel_cost`                                                                                                                     ",
            "		WHERE region_id=#{regionId}  AND `year`=#{year}  AND   month_of_year=#{month}                                                               ",
            "		GROUP BY province_name                                                                                                                      ",
            "	)pct ON pr.province_name=pct.province_name                                                                                                      ",
            "	WHERE pr.parent_id=#{regionId}                                                                                                                  ",
            ")temp                                                                                                                                              ",
            "</script>"
    })
    List<OperationGrossProfitRegionDTO> getGrossProfitByMonth(Integer regionId, Integer year, Integer month);

    @Select({
            "<script>",
            "SELECT region_name,collection_amount,payment_amount,salary,																	",
            "(IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))gross_profit,                                           ",
            "ROUND((IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))/IFNULL(collection_amount,0),2) gross_profit_rate ",
            "FROM(                                                                                                                          ",
            "   SELECT                                                                                                                      ",
            "   region_name,                                                                                                                ",
            "   SUM(collection_amount)  collection_amount,                                                                                  ",
            "   (SELECT SUM(payment_amount)    FROM `p_expenses` WHERE region_id=#{regionId}  AND `year`=#{year}   ) payment_amount,        ",
            "   (SELECT SUM(salary)    FROM `p_personnel_cost`   WHERE region_id=#{regionId}  AND `year`=#{year}   ) salary                 ",
            "   FROM `p_payment_collection`    WHERE region_id=#{regionId}  AND `year`=#{year}                                              ",
            ")temp                                                                                                                          ",
            "                                                                                                                               ",
            "UNION ALL                                                                                                                      ",
            "                                                                                                                               ",
            "SELECT region_name,collection_amount,payment_amount,salary,																	",
            "(IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))gross_profit,                                           ",
            "ROUND((IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))/IFNULL(collection_amount,0),2) gross_profit_rate ",
            "FROM                                                                                                                           ",
            "(                                                                                                                              ",
            "	SELECT pr.province_name region_name ,pc.collection_amount,ep.payment_amount,pct.salary                                      ",
            "	FROM p_region pr                                                                                                            ",
            "	LEFT JOIN                                                                                                                   ",
            "	(                                                                                                                           ",
            "		SELECT                                                                                                                  ",
            "		province_name,                                                                                                          ",
            "		SUM(collection_amount)  collection_amount                                                                               ",
            "		FROM `p_payment_collection`                                                                                             ",
            "		WHERE region_id=#{regionId}  AND `year`=#{year}                                                                         ",
            "		GROUP BY province_name                                                                                                  ",
            "	) pc ON pr.province_name=pc.province_name                                                                                   ",
            "	LEFT JOIN(                                                                                                                  ",
            "		SELECT province_name,SUM(payment_amount)payment_amount                                                                  ",
            "		FROM `p_expenses`                                                                                                       ",
            "		WHERE region_id=#{regionId}  AND `year`=#{year}                                                                         ",
            "		GROUP BY province_name                                                                                                  ",
            "	)ep ON pr.province_name=ep.province_name                                                                                    ",
            "	LEFT JOIN                                                                                                                   ",
            "	(                                                                                                                           ",
            "		SELECT  province_name,SUM(salary)salary                                                                                 ",
            "		FROM `p_personnel_cost`                                                                                                 ",
            "		WHERE region_id=#{regionId}  AND `year`=#{year}                                                                         ",
            "		GROUP BY province_name                                                                                                  ",
            "	)pct ON pr.province_name=pct.province_name                                                                                  ",
            "	WHERE pr.parent_id=#{regionId}                                                                                              ",
            ")temp                                                                                                                          ",
            "</script>"
    })
    List<OperationGrossProfitRegionDTO> getGrossProfitByYear(Integer regionId, Integer year);


    @Select({
            "<script>",
            "SELECT 																																					",
            "region_name,                                                                                                                                               ",
            "contract_amount,                                                                                                                                           ",
            "collection_amount,		                                                                                                                                    ",
            "(IFNULL(contract_amount,0)-IFNULL(collection_amount,0))collection_un_amount,                                                                               ",
            "payment_amount                                                                                                                                             ",
            "FROM(                                                                                                                                                      ",
            "	SELECT                                                                                                                                                  ",
            "	region_name,                                                                                                                                            ",
            "	SUM(contract_amount)  contract_amount,                                                                                                                  ",
            "	(SELECT SUM(collection_amount) FROM p_payment_collection  WHERE region_id=#{regionId} AND `year`=#{year} AND month_of_year=#{month} )collection_amount, ",
            "	(SELECT SUM(payment_amount)    FROM `p_expenses`          WHERE region_id=#{regionId} AND `year`=#{year} AND month_of_year=#{month} )payment_amount     ",
            "	FROM `p_new_sign`   WHERE region_id=#{regionId} AND `year`=#{year} AND month_of_year=#{month}                                                           ",
            ")temp                                                                                                                                                      ",
            "                                                                                                                                                           ",
            "UNION ALL                                                                                                                                                  ",
            "                                                                                                                                                           ",
            "SELECT 																																					",
            "region_name,                                                                                                                                               ",
            "contract_amount,                                                                                                                                           ",
            "collection_amount,		                                                                                                                                    ",
            "(IFNULL(contract_amount,0)-IFNULL(collection_amount,0))collection_un_amount,                                                                               ",
            "payment_amount                                                                                                                                             ",
            "FROM                                                                                                                                                       ",
            "(                                                                                                                                                          ",
            "	SELECT pr.province_name region_name ,ns.contract_amount,pc.collection_amount,ep.payment_amount                                                          ",
            "	FROM p_region pr                                                                                                                                        ",
            "	LEFT JOIN                                                                                                                                               ",
            "	(                                                                                                                                                       ",
            "		SELECT                                                                                                                                              ",
            "		province_name,                                                                                                                                      ",
            "		SUM(contract_amount)  contract_amount                                                                                                               ",
            "		FROM `p_new_sign`                                                                                                                                   ",
            "		WHERE region_id=#{regionId} AND `year`=#{year} AND month_of_year=#{month}                                                                           ",
            "		GROUP BY province_name                                                                                                                              ",
            "	) ns ON pr.province_name=ns.province_name                                                                                                               ",
            "	LEFT JOIN(                                                                                                                                              ",
            "		SELECT                                                                                                                                              ",
            "		province_name,                                                                                                                                      ",
            "		SUM(collection_amount) collection_amount                                                                                                            ",
            "		FROM p_payment_collection                                                                                                                           ",
            "		WHERE region_id=#{regionId} AND `year`=#{year} AND month_of_year=#{month}                                                                           ",
            "		GROUP BY province_name                                                                                                                              ",
            "	)pc ON pr.province_name=pc.province_name                                                                                                                ",
            "	LEFT JOIN                                                                                                                                               ",
            "	(                                                                                                                                                       ",
            "		SELECT                                                                                                                                              ",
            "		province_name,                                                                                                                                      ",
            "		SUM(payment_amount) payment_amount                                                                                                                  ",
            "		FROM `p_expenses`                                                                                                                                   ",
            "		WHERE region_id=#{regionId} AND `year`=#{year} AND month_of_year=#{month}                                                                           ",
            "		GROUP BY province_name                                                                                                                              ",
            "	)ep ON pr.province_name=ep.province_name                                                                                                                ",
            "	WHERE pr.parent_id=#{regionId}                                                                                                                          ",
            ")temp                                                                                                                                                      ",
            "</script>"
    })
    List<OperationFinanceRegionDTO> getFinanceByMonth(Integer regionId, Integer year, Integer month);


    @Select({
            "<script>",
            "SELECT 																														  	",
            "region_name,                                                                                                                       ",
            "contract_amount,                                                                                                                   ",
            "collection_amount,		                                                                                                            ",
            "(IFNULL(contract_amount,0)-IFNULL(collection_amount,0))collection_un_amount,                                                       ",
            "payment_amount                                                                                                                     ",
            "FROM(                                                                                                                              ",
            "	SELECT                                                                                                                          ",
            "	region_name,                                                                                                                    ",
            "	SUM(contract_amount)  contract_amount,                                                                                          ",
            "	(SELECT SUM(collection_amount) FROM p_payment_collection  WHERE region_id=#{regionId} AND `year`=#{year}   )collection_amount,  ",
            "	(SELECT SUM(payment_amount)    FROM `p_expenses`          WHERE region_id=#{regionId} AND `year`=#{year}   )payment_amount      ",
            "	FROM `p_new_sign`   WHERE region_id=#{regionId} AND `year`=#{year}                                                              ",
            ")temp                                                                                                                              ",
            "                                                                                                                                   ",
            "UNION ALL                                                                                                                          ",
            "                                                                                                                                   ",
            "SELECT 																															",
            "region_name,                                                                                                                       ",
            "contract_amount,                                                                                                                   ",
            "collection_amount,		                                                                                                            ",
            "(IFNULL(contract_amount,0)-IFNULL(collection_amount,0))collection_un_amount,                                                       ",
            "payment_amount                                                                                                                     ",
            "FROM                                                                                                                               ",
            "(                                                                                                                                  ",
            "	SELECT pr.province_name region_name ,ns.contract_amount,pc.collection_amount,ep.payment_amount                                  ",
            "	FROM p_region pr                                                                                                                ",
            "	LEFT JOIN                                                                                                                       ",
            "	(                                                                                                                               ",
            "		SELECT                                                                                                                      ",
            "		province_name,                                                                                                              ",
            "		SUM(contract_amount)  contract_amount                                                                                       ",
            "		FROM `p_new_sign`                                                                                                           ",
            "		WHERE region_id=#{regionId} AND `year`=#{year}                                                                              ",
            "		GROUP BY province_name                                                                                                      ",
            "	) ns ON pr.province_name=ns.province_name                                                                                       ",
            "	LEFT JOIN(                                                                                                                      ",
            "		SELECT                                                                                                                      ",
            "		province_name,                                                                                                              ",
            "		SUM(collection_amount) collection_amount                                                                                    ",
            "		FROM p_payment_collection                                                                                                   ",
            "		WHERE region_id=#{regionId} AND `year`=#{year}                                                                              ",
            "		GROUP BY province_name                                                                                                      ",
            "	)pc ON pr.province_name=pc.province_name                                                                                        ",
            "	LEFT JOIN                                                                                                                       ",
            "	(                                                                                                                               ",
            "		SELECT                                                                                                                      ",
            "		province_name,                                                                                                              ",
            "		SUM(payment_amount) payment_amount                                                                                          ",
            "		FROM `p_expenses`                                                                                                           ",
            "		WHERE region_id=#{regionId} AND `year`=#{year}                                                                              ",
            "		GROUP BY province_name                                                                                                      ",
            "	)ep ON pr.province_name=ep.province_name                                                                                        ",
            "	WHERE pr.parent_id=#{regionId}                                                                                                  ",
            ")temp                                                                                                                              ",
            "</script>"
    })
    List<OperationFinanceRegionDTO> getFinanceByYear(Integer regionId, Integer year);


    @Select({
            "<script>",
            "SELECT province_name,product_type_name,SUM(contract_amount) contract_amount,COUNT(id)contract_number ",
            "FROM p_business_opportunity                                                                          ",
            "<where>                                                                                              ",
            "   <if test='regionId!=null'>                                                                        ",
            "     and  region_id=#{regionId}                                                                      ",
            "   </if>                                                                                             ",
            "   <if test='year!=null'>                                                                            ",
            "     and  year=#{year}                                                                               ",
            "   </if>                                                                                             ",
            "   <if test='month!=null'>                                                                           ",
            "     and  month_of_year=#{month}                                                                     ",
            "   </if>                                                                                             ",
            "</where>                                                                                             ",
            "GROUP BY province_name,product_type_name                                                             ",
            "</script>"
    })
    List<BusinessOpportunityLineProvinceDTO> getBusinessOpportunities(Integer regionId, Integer year, Integer month);



    @Select({
            "<script>",
            "SELECT *,(stage_30+stage_40+stage_50+stage_60+stage_70+stage_80+stage_90)total_amount FROM 			",
            "(                                                                                                      ",
            "	SELECT province_name,                                                                               ",
            "	(CASE a.stage        WHEN '30%' THEN a.contract_amount        ELSE 0    END) 'stage_30',            ",
            "	(CASE a.stage        WHEN '40%' THEN a.contract_amount        ELSE 0    END) 'stage_40',            ",
            "	(CASE a.stage        WHEN '50%' THEN a.contract_amount        ELSE 0    END) 'stage_50',            ",
            "	(CASE a.stage        WHEN '60%' THEN a.contract_amount        ELSE 0    END) 'stage_60',            ",
            "	(CASE a.stage        WHEN '70%' THEN a.contract_amount        ELSE 0    END) 'stage_70',            ",
            "	(CASE a.stage        WHEN '80%' THEN a.contract_amount        ELSE 0    END) 'stage_80',            ",
            "	(CASE a.stage        WHEN '90%' THEN a.contract_amount        ELSE 0    END) 'stage_90'             ",
            "	FROM                                                                                                ",
            "	(                                                                                                   ",
            "		SELECT province_name,CONCAT(ROUND(stage*100,0),'%')stage ,SUM(contract_amount)contract_amount   ",
            "		FROM(                                                                                           ",
            "				SELECT                                                                                  ",
            "				province_name,                                                                          ",
            "				CASE                                                                                    ",
            "				WHEN stage>=0.9 THEN 0.9                                                                ",
            "				WHEN stage>=0.8 THEN 0.8                                                                ",
            "				WHEN stage>=0.7 THEN 0.7                                                                ",
            "				WHEN stage>=0.6 THEN 0.6                                                                ",
            "				WHEN stage>=0.5 THEN 0.5                                                                ",
            "				WHEN stage>=0.4 THEN 0.4                                                                ",
            "				WHEN stage>=0.3 THEN 0.3                                                                ",
            "				END stage,                                                                              ",
            "				contract_amount                                                                         ",
            "				FROM p_business_opportunity                                                             ",
            "				<where>                                                                                 ",
            "					stage >=0.3                                                                         ",
            "					<if test='regionId!=null'>                                                          ",
            "						and  region_id=#{regionId}                                                      ",
            "					</if>                                                                               ",
            "					<if test='year!=null'>                                                              ",
            "						and  year=#{year}                                                               ",
            "					</if>                                                                               ",
            "					<if test='month!=null'>                                                             ",
            "						and  month_of_year=#{month}                                                     ",
            "					</if>                                                                               ",
            "				</where>                                                                                ",
            "		)temp GROUP BY province_name,stage                                                              ",
            "	)a GROUP BY province_name                                                                           ",
            ")temp                                                                                                  ",
            "</script>"
    })
    List<BusinessOpportunity30RegionDTO> getBusinessOpportunitiesStage30(Integer regionId, Integer year, Integer month);


    @Select({
            "<script>",
            " SELECT 																																						",
            " region_name,																					                                                                ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='未启动'   AND region_id=#{regionId})a,                                                         ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='沟通中'   AND region_id=#{regionId})b,                                                         ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='沟通100%' AND region_id=#{regionId})c,                                                         ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='拍摄中'   AND region_id=#{regionId})d,                                                         ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='拍摄100%' AND region_id=#{regionId})e,                                                         ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='制作中'   AND region_id=#{regionId})f,                                                         ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='制作100%' AND region_id=#{regionId})g,                                                         ",
            " COUNT(id) total_count                                                                                                                                         ",
            " FROM p_po_course WHERE course_schedule_name &lt;&gt; '已完结' AND region_id=#{regionId}                                                                       ",
            "                                                                                                                                                               ",
            " UNION ALL                                                                                                                                                     ",
            "                                                                                                                                                               ",
            " SELECT pc.province_name region_name,a.a,b.b,c.c,d.d,e.e,f.f,g.g,h.total_count                                                                                 ",
            " FROM p_po_course pc                                                                                                                                           ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) a FROM p_po_course WHERE course_schedule_name='未启动'   AND region_id=#{regionId} GROUP BY province_name                                               ",
            " ) a ON pc. province_name=a.province_name                                                                                                                      ",
            "                                                                                                                                                               ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) b FROM p_po_course WHERE course_schedule_name='沟通中'   AND region_id=#{regionId} GROUP BY province_name                       ",
            " ) b ON pc. province_name=b.province_name                                                                                                                      ",
            "                                                                                                                                                               ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) c FROM p_po_course WHERE course_schedule_name='沟通100%'   AND region_id=#{regionId}  GROUP BY province_name                    ",
            " ) c ON pc. province_name=c.province_name                                                                                                                      ",
            "                                                                                                                                                               ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) d FROM p_po_course WHERE course_schedule_name='拍摄中'   AND region_id=#{regionId}  GROUP BY province_name                      ",
            " ) d ON pc. province_name=d.province_name                                                                                                                      ",
            "                                                                                                                                                               ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) e FROM p_po_course WHERE course_schedule_name='拍摄100%'   AND region_id=#{regionId}  GROUP BY province_name                    ",
            " ) e ON pc. province_name=e.province_name                                                                                                                      ",
            "                                                                                                                                                               ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) f FROM p_po_course WHERE course_schedule_name='制作中'   AND region_id=#{regionId}  GROUP BY province_name                      ",
            " ) f ON pc. province_name=f.province_name                                                                                                                      ",
            "                                                                                                                                                               ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) g FROM p_po_course WHERE course_schedule_name='制作100%'   AND region_id=#{regionId}  GROUP BY province_name                    ",
            " ) g ON pc. province_name=g.province_name                                                                                                                      ",
            " LEFT JOIN(                                                                                                                                                    ",
            " SELECT province_name,COUNT(*) total_count FROM p_po_course WHERE course_schedule_name &lt;&gt; '已完结'   AND region_id=#{regionId}  GROUP BY province_name   ",
            " )h ON pc. province_name=h.province_name                                                                                                                       ",
            " WHERE region_id=#{regionId}                                                                                                                                   ",
            "</script>"
    })
    List<OnLineStockRegionDTO> getOnLineCourseStock(Integer regionId);


    @Select("select month_of_year month,round(sum(capacity)/60,2) capacity  from p_course_capacity where year=year(now()) and region_id=#{regionId}")
    List<CourseCapacityLineRegionDTO> getCourseCapacity(Integer regionId);
}
